package com.sud.framework.system.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.*;
import java.util.*;

/**
 * 功能：代码生成工具
 * 作者：SUD
 * 时间：2018/5/10 14:44
 */
public class ModelGeneratorUtil {
    private static Logger logger = Logger.getLogger(ModelGeneratorUtil.class);
    private static Properties properties;

    public static void main(String[] args){
        try {
            String path = Thread.currentThread().getClass().getResource("/").getPath();
            String prefix = path.substring(1,path.indexOf("target")).replace("/", "\\");
            //指定生成文件存放的绝对路径
            String modelPath = prefix + "src\\main\\java\\com\\sud\\demo\\";
            //指定生成的bean存放的包名
            String packageName = "com.sud.demo";

            /*Connection con = ModelGeneratorUtil.getConnection();
            List<Map<String,String>> modelNames = new ArrayList<>();
            Map<String,String> map = new HashMap<>();
            map.put("modelName","Wechat");
            map.put("tableName","bvc_wechat");
            modelNames.add(map);
            generatorModel(modelNames,modelPath,packageName,con);*/

            generatorCommon(modelPath,packageName);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * 生成model文件_通过jdbc搞定
     * model 与 表名称的对照关系是 第一个下划线是表前缀， 也就是说 a_user_info这个表 生成的model名称是UserInfo
     * @param modelPath model存储路径根目录
     * @param packageName model所在包
     * @throws Exception
     */
    private static void generatorCommon(String modelPath,String packageName) throws Exception{

        Connection con = getConnection();
        Map<String,String> strings = new HashMap<String,String>();

        //将要生成的model名称与表名称列表
        List<Map<String,String>> modelNames = new ArrayList<Map<String,String>>();
        //读取数据库中表列表
        DatabaseMetaData metaData = con.getMetaData();
        ResultSet resultSet = metaData.getTables(null, "%","%",new String[]{"TABLE"});
        String tableName = null;
        String[] tableNames = null;
        StringBuffer modelName = null;
        Map<String,String> modelMap = null;
        while (resultSet.next()) {
            modelMap = new HashMap<String,String>();
            modelName = new StringBuffer();
            //获取表明
            tableName = resultSet.getString("TABLE_NAME");
            //使用下划线分隔表明
            tableNames = tableName.split("_");
            if(tableNames.length>1){
                // 因为第一个下划线之前是表前缀，所以model名称生成从第一个下划线以后开始
                for(int i=1;i<tableNames.length;i++){
                    if(tableNames[i].length()>1){
                        //首字母大写
                        modelName.append(tableNames[i].substring(0,1).toUpperCase());
                        //其余都是小写
                        modelName.append(tableNames[i].substring(1).toLowerCase());
                    }else{
                        modelName.append(tableNames[i].toUpperCase());
                    }
                }
            }else{
                //不包含下划线时
                if(tableName.length()>1){
                    //首字母大写
                    modelName.append(tableName.substring(0,1).toUpperCase());
                    //其余都是小写
                    modelName.append(tableName.substring(1).toLowerCase());
                }else{
                    modelName.append(tableName.toUpperCase());
                }
            }

            if(modelName.length()>0){
                //如果去掉前缀之后 model有重复的名称，则给有重复的添加前缀
                if(strings.get(modelName.toString().toLowerCase()) != null){
                    String tableNamePrefix = tableName.split("_")[0];
                    tableNamePrefix = tableNamePrefix.substring(0,1).toUpperCase()+tableNamePrefix.substring(1);
                    modelName = new StringBuffer(tableNamePrefix+modelName.toString());
                }
                modelMap.put("modelName",modelName.toString());
                modelMap.put("tableName",tableName);
                modelNames.add(modelMap);

                strings.put(modelName.toString().toLowerCase(),tableName);
            }
        }
        logger.info(JSONObject.toJSONString(modelNames));
        generatorModel(modelNames,modelPath,packageName,con);
    }

    /**
     * 生成model
     * @param modelNames
     */
    private static void generatorModel(List<Map<String,String>> modelNames,String modelPath,String packageName,Connection con) throws Exception{
        String modelName = null;
        String tableName = null;
        String sql = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String field = null;
        String[] fields = null;
        StringBuffer modelProperty = null;
        StringBuffer propertyMethod = null;
        String propertyType = null;
        String type = null;
        String comment = null;
        StringBuffer modelContent = null;
        String content = null;
        Properties properties = getProperties();
        String tableSchema = properties.getProperty("jdbc.tableSchema");
        //导入包清单
        StringBuffer importContent = null;
        //查询sql别名清单
        StringBuffer selectSqlColumn = null;

        String sqlCol = "select column_name,column_comment,data_type,column_key,extra from information_schema.columns where table_schema ='"+tableSchema+"'";
        String sqlTable = "select TABLE_NAME,TABLE_COMMENT from information_schema.TABLES where table_schema ='"+tableSchema+"'";
        String sqlTab = null;
        String tableComment = null;
        String extra = null;
        String columnKey = null;
        //判断model包是否存在， 如果不存在就创建
        File file = new File(modelPath+"model");
        if(!file .exists()  && !file .isDirectory()){
            file.mkdir();
        }
        for (Map<String,String> map : modelNames){
            modelContent = new StringBuffer();
            importContent = new StringBuffer();
            selectSqlColumn = new StringBuffer();
            propertyMethod = new StringBuffer();
            modelName = map.get("modelName");
            tableName = map.get("tableName");
            sql = sqlCol + " and table_name = '"+tableName+"'";
            sqlTab = sqlTable + " and table_name = '"+tableName+"'";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                modelProperty = new StringBuffer();
                propertyType = null;
                field = rs.getString("column_name").toLowerCase();
                type = rs.getString("data_type");
                comment = rs.getString("column_comment");
                //自增标识
                extra = rs.getString("extra");
                //主键标识
                columnKey = rs.getString("column_key");

                //加注释
                modelContent.append("    /**\r\n");
                modelContent.append("    * "+comment+"\r\n");
                modelContent.append("    */\r\n");
                modelContent.append("    private ");
                propertyType = mysqlTypeToJavaType(type,importContent);
                modelContent.append(propertyType);
                if(field.indexOf("_")>-1){
                    fields = field.split("_");
                    for (int j=0;j<fields.length;j++){
                        if(StringUtils.isNotEmpty(fields[j])){
                            if(modelProperty.length()>0){
                                modelProperty.append(fields[j].substring(0,1).toUpperCase());
                                modelProperty.append(fields[j].substring(1));
                            }else{
                                modelProperty.append(fields[j]);
                            }
                        }
                    }
                }else{
                    if(modelProperty.length()>0){
                        modelProperty.append(field.substring(0,1).toUpperCase());
                        modelProperty.append(field.substring(1));
                    }else{
                        modelProperty.append(field);
                    }
                }
                modelContent.append(" ");
                modelContent.append(modelProperty);
                modelContent.append(";");
                modelContent.append("\r\n");

                if(StringUtils.isNotEmpty(columnKey)){
                    map.put("extra",extra);
                    map.put("columnKey",modelProperty.toString());
                    map.put("field",field);
                }


                propertyMethod.append("    public "+propertyType+" get"+modelProperty.toString().substring(0,1).toUpperCase()+modelProperty.toString().substring(1)+"() {\r\n");
                propertyMethod.append("        return "+modelProperty+";\r\n");
                propertyMethod.append("    }\r\n");
                propertyMethod.append("    public void set"+modelProperty.toString().substring(0,1).toUpperCase()+modelProperty.toString().substring(1)+"("+propertyType+" "+modelProperty+") {\r\n");
                propertyMethod.append("        this."+modelProperty+" = "+modelProperty+";\r\n");
                propertyMethod.append("    }\r\n");

                selectSqlColumn.append("        ");
                selectSqlColumn.append(field);
                selectSqlColumn.append(" as ");
                selectSqlColumn.append(modelProperty);
                selectSqlColumn.append(",\r\n");
            }

            //查询表注释
            ps = con.prepareStatement(sqlTab);
            rs = ps.executeQuery();
            while (rs.next()) {
                tableComment = rs.getString("TABLE_COMMENT");
            }
            if(StringUtils.isNotEmpty(tableComment)){
                content = "package "+packageName + ".model;\r\n" +
                        importContent.toString() + "\r\n" +
                        "/**\r\n"+
                        "* "+tableComment+"\r\n"+
                        "* 作者：AutoGeneration\r\n"+
                        "* 时间："+ DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n"+
                        "*/\r\n"+
                        "public class "+modelName+" {\r\n" +
                        modelContent.toString()+
                        propertyMethod.toString()+
                        "}";
            }else{
                content = "package "+packageName + ".model;\r\n" +
                        importContent.toString() + "\r\n" +
                        "public class "+modelName+" {\r\n" +
                        modelContent.toString()+
                        propertyMethod.toString()+
                        "}";
            }
            map.put("selectSqlColumn",selectSqlColumn.substring(0,selectSqlColumn.length()-3)+"\r\n");
            map.put("modelPackage",selectSqlColumn.toString());
            map.put("tableComment",StringUtils.isNotEmpty(tableComment) ? tableComment : "");

            logger.info(content);

            FileWriter fw = new FileWriter(modelPath+"model\\"+modelName + ".java");
            PrintWriter pw = new PrintWriter(fw);
            pw.println(content);
            pw.flush();
            pw.close();
        }
        generatorMapper(modelNames,modelPath,packageName,con);
    }
    /**
     * 生成mapper
     * @param modelNames
     */
    private static void generatorMapper(List<Map<String,String>> modelNames,String modelPath,String packageName,Connection con) throws Exception{
        String modelName = null;
        String model = null;
        String tableName = null;
        String selectSqlColumn = null;
        String selectSqlId = null;
        String tableComment = null;
        String modelPackage = null;
        StringBuffer sqlContent = null;
        String[] saveColumns = null;
        String saveColumn = null;
        String saveProperty = null;
        String columnKey = null;
        String extra = null;
        String field = null;
        StringBuffer sqlPart = null;
        File file = new File(modelPath+"mapper");
        if(!file .exists()  && !file .isDirectory()){
            file.mkdir();
        }
        for (Map<String,String> map : modelNames){
            sqlContent = new StringBuffer();
            modelName = map.get("modelName");
            model = modelName.substring(0,1).toLowerCase() + modelName.substring(1);
            tableName = map.get("tableName");
            selectSqlColumn = map.get("selectSqlColumn");
            tableComment = map.get("tableComment");
            columnKey = map.get("columnKey");
            extra = map.get("extra");
            field = map.get("field");
            selectSqlId = "select"+modelName+"Column";
            modelPackage = packageName+".model."+modelName;

            sqlContent.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n");
            sqlContent.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" >\r\n");
            sqlContent.append("<mapper namespace=\""+modelName+"\">\r\n");

            //设置公用sql片段
            sqlContent.append("    <!--\r\n");
            sqlContent.append("        功能：查询 "+tableComment+" 返回字段\r\n");
            sqlContent.append("        作者：AutoGeneration\r\n");
            sqlContent.append("        时间："+DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n");
            sqlContent.append("    -->\r\n");
            sqlContent.append("    <sql id=\""+selectSqlId+"\">\r\n");
            sqlContent.append(selectSqlColumn);
            sqlContent.append("    </sql>\r\n");

            //设置新增sql
            sqlContent.append("    <!--\r\n");
            sqlContent.append("        功能：新增 "+tableComment+" \r\n");
            sqlContent.append("        作者：AutoGeneration\r\n");
            sqlContent.append("        时间："+DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n");
            sqlContent.append("    -->\r\n");
            sqlContent.append("    <insert id=\"save"+modelName+"\">\r\n");
            sqlContent.append("        INSERT INTO "+tableName+" \r\n");
            sqlContent.append("        VALUES("+" \r\n");
            //sqlContent.append("        INSERT INTO "+tableName);
            sqlPart = new StringBuffer();
            saveColumns = selectSqlColumn.split(",");
            for(int j=0;j<saveColumns.length;j++){
                saveColumn = saveColumns[j];
                if(StringUtils.isNotEmpty(saveColumn)){
                    saveProperty = saveColumn.split("as")[1].trim();
                    if(StringUtils.isNotEmpty(columnKey) && columnKey.equals(saveProperty) && StringUtils.isNotEmpty(extra)){
                        //如果是自增主键 则不插入值
                        sqlPart.append("            null,\r\n");
                    }else{
                        sqlPart.append("            #{");
                        sqlPart.append(model);
                        sqlPart.append(".");
                        sqlPart.append(saveProperty);
                        sqlPart.append("},\r\n");
                    }
                }
            }
            sqlContent.append(sqlPart.substring(0,sqlPart.length()-3)+"\r\n");
            sqlContent.append("        )\r\n");
            sqlContent.append("    </insert>\r\n");


            //设置根据主键删除数据sql
            if(StringUtils.isNotEmpty(columnKey)){
                sqlContent.append("    <!--\r\n");
                sqlContent.append("        功能：根据主键删除 "+tableComment+" 数据\r\n");
                sqlContent.append("        作者：AutoGeneration\r\n");
                sqlContent.append("        时间："+DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n");
                sqlContent.append("    -->\r\n");
                sqlContent.append("    <delete id=\"remove"+modelName+"ById\">\r\n");
                sqlContent.append("        DELETE FROM\r\n");
                sqlContent.append("            "+tableName+"\r\n");
                sqlContent.append("        WHERE\r\n");
                sqlContent.append("            "+field+" = #{"+columnKey+"}\r\n");
                sqlContent.append("    </delete>\r\n");
            }
            //设置根据主键编辑数据sql
            if(StringUtils.isNotEmpty(columnKey)){

                sqlPart = new StringBuffer();

                sqlContent.append("    <!--\r\n");
                sqlContent.append("        功能：根据主键编辑 "+tableComment+" 数据\r\n");
                sqlContent.append("        作者：AutoGeneration\r\n");
                sqlContent.append("        时间："+DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n");
                sqlContent.append("    -->\r\n");
                sqlContent.append("    <update id=\"update"+modelName+"ById\">\r\n");
                sqlContent.append("        UPDATE \r\n");
                sqlContent.append("            "+tableName+"\r\n");
                sqlContent.append("        SET \r\n");

                for(int j=0;j<saveColumns.length;j++){
                    saveColumn = saveColumns[j];
                    if(StringUtils.isNotEmpty(saveColumn)){
                        saveProperty = saveColumn.split("as")[1].trim();
                        saveColumn = saveColumn.split("as")[0].trim();
                        if(!columnKey.equals(saveProperty)){
                            sqlPart.append("            "+saveColumn+" = #{");
                            sqlPart.append(model);
                            sqlPart.append(".");
                            sqlPart.append(saveProperty);
                            sqlPart.append("},\r\n");
                        }
                    }
                }
                sqlContent.append(sqlPart.substring(0,sqlPart.length()-3)+"\r\n");
                sqlContent.append("        WHERE\r\n");
                sqlContent.append("            "+field+" = #{");
                sqlContent.append(model);
                sqlContent.append(".");
                sqlContent.append(columnKey);
                sqlContent.append("}\r\n");

                sqlContent.append("    </update>\r\n");
            }


            //设置查询列表sql
            if(StringUtils.isNotEmpty(columnKey)){
                //如果有主键时，根据主键查询单个对象
                sqlContent.append("    <!--\r\n");
                sqlContent.append("        功能：根据主键查询 "+tableComment+" 数据\r\n");
                sqlContent.append("        作者：AutoGeneration\r\n");
                sqlContent.append("        时间："+DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n");
                sqlContent.append("    -->\r\n");
                sqlContent.append("    <select id=\"query"+modelName+"ById\" resultType=\""+modelPackage+"\">\r\n");
                sqlContent.append("        SELECT\r\n");
                sqlContent.append("            <include refid=\""+selectSqlId+"\" />\r\n");
                sqlContent.append("        FROM\r\n");
                sqlContent.append("            "+tableName+"\r\n");
                sqlContent.append("        WHERE\r\n");
                sqlContent.append("            "+field+" = #{"+columnKey+"}\r\n");
                sqlContent.append("    </select>\r\n");
            }
            sqlContent.append("    <!--\r\n");
            sqlContent.append("        功能：查询所有 "+tableComment+" 数据\r\n");
            sqlContent.append("        作者：AutoGeneration\r\n");
            sqlContent.append("        时间："+DateUtil.getCurrentTime(DateUtil.FORMAT_19)+"\r\n");
            sqlContent.append("    -->\r\n");
            sqlContent.append("    <select id=\"query"+modelName+"ByAllList\" resultType=\""+modelPackage+"\">\r\n");
            sqlContent.append("        SELECT\r\n");
            sqlContent.append("            <include refid=\""+selectSqlId+"\" />\r\n");
            sqlContent.append("        FROM\r\n");
            sqlContent.append("            "+tableName+"\r\n");
            sqlContent.append("    </select>\r\n");

            sqlContent.append("</mapper>");

            FileWriter fw = new FileWriter(modelPath+"mapper\\"+modelName + "Mapper.xml");
            PrintWriter pw = new PrintWriter(fw);
            pw.println(sqlContent.toString());
            pw.flush();
            pw.close();
        }
    }
    /**
     * 获取jdbc链接
     * @return
     * @throws Exception
     */
    private static Connection getConnection() throws Exception{
        //从资源文件中读取jdbc配置
        Properties properties = getProperties();
        String driverClassName = properties.getProperty("jdbc.driverClassName");
        Class.forName(driverClassName);
        Class.forName(driverClassName).newInstance();
        String connectUrl = properties.getProperty("jdbc.url");
        connectUrl += "&user=" + properties.getProperty("jdbc.username");
        connectUrl += "&password=" + properties.getProperty("jdbc.password");
        Connection con = DriverManager.getConnection(connectUrl);
        return con;
    }

    /**
     * 根据mysql字段类型转化java类型
     * @param sqlType
     * @return
     */
    private static String mysqlTypeToJavaType(String sqlType,StringBuffer importContent){
        if(sqlType.equals("int") || sqlType.equals("tinyint") || sqlType.equals("smallint")
                || sqlType.equals("bigint")){
            return "Integer";
        }else if(sqlType.equals("double")){
            return "Double";
        }else if(sqlType.equals("float")){
            return "Float";
        }else if(sqlType.equals("decimal")){
            importContent.append("import java.math.BigDecimal;\r\n");
            return "BigDecimal";
        }else if(sqlType.equals("bit")){
            return "Boolean";
        }else if(sqlType.equals("varchar") || sqlType.equals("varchar2") || sqlType.equals("char")
                || sqlType.equals("mediumtext") || sqlType.equals("text") || sqlType.equals("tinytext")
                || sqlType.equals("longtext")){
            return "String";
        }else if(sqlType.equals("timestamp") || sqlType.equals("date") || sqlType.equals("time")
                || sqlType.equals("year") || sqlType.equals("datetime")){
            importContent.append("import java.util.Date;\r\n");
            return "Date";
        }else if(sqlType.equals("tinyblob") || sqlType.equals("blob") || sqlType.equals("mediumblob")
                || sqlType.equals("longblob") || sqlType.equals("datetime")){
            //importContent.append("import java.util.Date;\r\n");
            return "byte[]";
        }
        return null;

    }
    private static Properties getProperties() throws Exception{
        if(properties!=null){
            return properties;
        }else{
            Properties properties1 = new Properties();
            InputStream input = Thread.currentThread().getClass().getResourceAsStream("/jdbc.properties");
            properties1.load(input);
            properties = properties1;
        }
        return properties;
    }
}
